package chatserver.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import androidchat.com.common.User;

public class DBUtils {
	public static final String username = "root";
	public static final String password = "admin";
	Connection connection;

	public DBUtils() {
		try {
			Class.forName("com.mysql.jdbc.Driver");

		} catch (ClassNotFoundException e) {

			e.printStackTrace();
		}
	}

	public boolean register(User user) {
		try {
			connection = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/AndroidChat", username,
					password);

			String sql = "insert into user(account,nickname,password,avatar,sex,age) "
					+ "values(?,?,?,?,?,?)";
			PreparedStatement ps = connection.prepareStatement(sql);
			ps.setString(1, user.getAccount());
			ps.setString(2, user.getNick());
			ps.setString(3, user.getPassword());
			ps.setString(4, user.getAvatar());
			ps.setString(5, user.getSex());
			ps.setInt(6, user.getAge());
			int rs = ps.executeUpdate();

			connection.close();
			if (rs > 0)
				return true;

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public boolean login(User user) {

		try {
			connection = DriverManager.getConnection(
					"jdbc:mysql://localhost/AndroidChat", username, password);
			String sql = "select * from user where account=? and password=?";
			PreparedStatement ps = connection.prepareStatement(sql);
			ps.setString(1, user.getAccount());
			ps.setString(2, user.getPassword());
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				connection.close();
				return true;
			} else {
				connection.close();
				return false;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} 
		return false;
	}

	// jiji
	public String getFriendListString(String account) {

		try {
			connection = DriverManager.getConnection(
					"jdbc:mysql://localhost/AndroidChat", username, password);
			String friend = "select distinct * from friend as t1,user as t2 "
					+ "where t1.account=? and t1.friend=t2.account";
			String group = "select distinct * from gmember as t1,chatgroup as t2 "
					+ "where t1.account=? and t1.gaccount=t2.gaccount";
			PreparedStatement ps = connection.prepareStatement(friend);
			ps.setString(1, account);
			String result = "";
			ResultSet rs1 = ps.executeQuery();
			while (rs1.next()) {
				result += rs1.getString("friend") + "/"
						+ rs1.getString("nickname") + "/"
						+ rs1.getString("avatar") + "/"
						+ rs1.getString("description") + "/"
						+ rs1.getString("remark") + "/"
						+ rs1.getString("isonline") + "#";
			}
			result += ",";
			ps = connection.prepareStatement(group);
			ps.setString(1, account);
			ResultSet rs2 = ps.executeQuery();
			while (rs2.next()) {
				result += rs2.getString("gaccount") + "/"
						+ rs2.getString("gnickname") + "/"
						+ rs2.getString("gdescription") + "/"
						+ rs2.getString("gmanager") + "#";

			}

			return result;

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return "";

	}

	public String getUserInfo(String account) {
		String result = "";
		try {
			connection = DriverManager.getConnection(
					"jdbc:mysql://localhost/AndroidChat", username, password);
			String sql = "select * from user where account=?";
			PreparedStatement ps = connection.prepareStatement(sql);
			ps.setString(1, account);
			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				result += rs.getString("account") + "/"
						+ rs.getString("nickname") + "/"
						+ rs.getString("avatar") + "/"
						+ rs.getString("description") + "/"
						+ rs.getString("isonline");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}

	public String getGroupinfo(String gaccount) {

		String result = "";
		try {
			connection = DriverManager.getConnection(
					"jdbc:mysql://localhost/AndroidChat", username, password);
			String sql = "select * from chatgroup where gaccount=?";
			PreparedStatement ps = connection.prepareStatement(sql);
			ps.setString(1, gaccount);
			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				result += rs.getString("gaccount") + "/"
						+ rs.getString("gnickname") + "/"
						+ rs.getString("gdescription") + "/"
						+ rs.getString("gmanager");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}

	public boolean addFriend(String account, String friend) {
		try {
			if (!existedUser(account))
				return false;
			if (!isFriend(account, friend)) {
				connection = DriverManager.getConnection(
						"jdbc:mysql://localhost/AndroidChat", username,
						password);
				String sql = "insert into friend(account,friend) values(?,?)";
				PreparedStatement ps;
				ps = connection.prepareStatement(sql);
				ps.setString(1, account);
				ps.setString(2, friend);
				ps.executeUpdate();
				return true;
			} else {
				return false;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}

	public boolean addGroup(String gaccount, String account) {
		boolean b = false;
		if (existedGroup(gaccount)) {
			try {
				System.out.println("group is exist");
				connection = DriverManager.getConnection(
						"jdbc:mysql://localhost/AndroidChat", username,
						password);
				String sql = "insert into gmember(gaccount,account) values(?,?)";
				PreparedStatement ps;
				ps = connection.prepareStatement(sql);
				ps.setString(1, gaccount);
				ps.setString(2, account);
				ps.executeUpdate();
				b = true;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		} else {
			System.out.println("group is not exist");
			b = false;
		}
		return b;

	}

	public boolean delFriend(String account, String friend) {
		try {
			if (!isFriend(account, friend))
				return false;
			connection = DriverManager.getConnection(
					"jdbc:mysql://localhost/AndroidChat", username, password);
			String sql = "delete from friend where account=? and friend=?";
			PreparedStatement ps = connection.prepareStatement(sql);
			ps.setString(1, account);
			ps.setString(2, friend);
			ps.executeUpdate();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}

	public boolean isFriend(String account, String friend) {
		try {
			connection = DriverManager.getConnection(
					"jdbc:mysql://localhost/AndroidChat", username, password);
			String sql = "select * from friend where account=? and friend=?";
			PreparedStatement ps = connection.prepareStatement(sql);
			ps.setString(1, account);
			ps.setString(2, friend);
			ResultSet rs = ps.executeQuery();
			if (rs.next())
				return true;
			else
				return false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}

	public boolean existedGroup(String gaccount) {
		try {
			System.out.println("gaccount is " + gaccount);
			connection = DriverManager.getConnection(
					"jdbc:mysql://localhost/AndroidChat", username, password);
			String sql = "select * from chatgroup where gaccount=?";
			PreparedStatement ps = connection.prepareStatement(sql);
			ps.setString(1, gaccount);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				System.out.println("have rs");
				return true;
			} else {
				System.out.println("rs is empty");
				return false;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}

	public boolean existedUser(String account) {
		try {
			connection = DriverManager.getConnection(
					"jdbc:mysql://localhost/AndroidChat", username, password);
			String sql = "select * from user where account=?";
			PreparedStatement ps = connection.prepareStatement(sql);
			ps.setString(1, account);
			ResultSet rs = ps.executeQuery();
			if (rs.next())
				return true;
			else
				return false;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return false;
	}

	public void updateRemark(String account, String friend, String remark) {
		try {
			connection = DriverManager.getConnection(
					"jdbc:mysql://localhost/AndroidChat", username, password);
			String sql = "update friend set remark=? where account=? and friend=?";
			PreparedStatement ps = connection.prepareStatement(sql);
			ps.setString(1, remark);
			ps.setString(2, account);
			ps.setString(3, friend);
			ps.executeUpdate();
			System.out.println("remark is " + remark);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public void updateGroupDes(String manager, String gaccount, String content) {
		try {
			connection = DriverManager.getConnection(
					"jdbc:mysql://localhost/AndroidChat", username, password);
			String sql = "update chatgroup set gdescription=? where gaccount=? and gmanager=?";
			PreparedStatement ps = connection.prepareStatement(sql);
			ps.setString(1, content);
			ps.setString(2, gaccount);
			ps.setString(3, manager);
			ps.executeUpdate();
			System.out.println("gdes is " + content);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

	public void deleteGroup(String myaccount, String gaccount, String manager) {

		try {
			connection = DriverManager.getConnection(
					"jdbc:mysql://localhost/AndroidChat", username, password);
			if (myaccount == manager) {
				String sql = "delete  from chatgroup where gaccount=?";
				String sql1 = "delete from gmember where gaccount=?";
				PreparedStatement ps = connection.prepareStatement(sql);
				PreparedStatement ps1 = connection.prepareStatement(sql1);
				ps.setString(1, gaccount);
				ps.executeUpdate();
				ps1.setString(1, gaccount);
				ps1.executeUpdate();

				System.out.println("myaccount ==manager ");
			} else {
				String sql2 = "delete from gmember where account=? and gaccount=?";
				PreparedStatement ps2 = connection.prepareStatement(sql2);
				System.out.println("my!=manager account=" + myaccount);
				ps2.setString(1, myaccount);
				ps2.setString(2, gaccount);
				ps2.executeUpdate();
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}
